- frmBusCardHolder_Student.vb
- project /
1 Imports System.Data.SqlClient
2 Public Class frmBusCardHolder_Student
3 Private Sub auto()
4 Try
5 Dim Num As Integer = 0
6 con = New SqlConnection(cs)
7 con.Open()
8 Dim sql As String = ("SELECT MAX(BCH_ID) FROM BusCardHolder_Student")
9 cmd = New SqlCommand(sql)
10 cmd.Connection = con
11 If (IsDBNull(cmd.ExecuteScalar)) Then
12 Num = 1
13 txtID.Text = Num.ToString
14 Else
15 Num = cmd.ExecuteScalar + 1
16 txtID.Text = Num.ToString
17 End If
18 cmd.Dispose()
19 con.Close()
20 con.Dispose()
21 Catch ex As Exception
22 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
23 End Try
24 End Sub
25 Sub fillLocationName()
26 Try
27 Dim CN As New SqlConnection(cs)
28 CN.Open()
29 adp = New SqlDataAdapter()
30 adp.SelectCommand = New SqlCommand("SELECT distinct RTRIM(LocationName) FROM Location", CN)
31 ds = New DataSet("ds")
32 adp.Fill(ds)
33 dtable = ds.Tables(0)
34 cmbLocationName.Items.Clear()
35 For Each drow As DataRow In dtable.Rows
36 cmbLocationName.Items.Add(drow(0).ToString())
37 Next
38
39 Catch ex As Exception
40 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
41 End Try
42 End Sub
43 Sub fillBusNo()
44 Try
45 Dim CN As New SqlConnection(cs)
46 CN.Open()
47 adp = New SqlDataAdapter()
48 adp.SelectCommand = New SqlCommand("SELECT distinct RTRIM(BusNo) FROM BusInfo", CN)
49 ds = New DataSet("ds")
50 adp.Fill(ds)
51 dtable = ds.Tables(0)
52 cmbBusNo.Items.Clear()
53 For Each drow As DataRow In dtable.Rows
54 cmbBusNo.Items.Add(drow(0).ToString())
55 Next
56
57 Catch ex As Exception
58 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
59 End Try
60 End Sub
61 Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
62 Me.Close()
63 End Sub
64 Sub Reset()
65 dtpJoiningDate.Text = Today
66 cmbStatus.SelectedIndex = -1
67 cmbBusNo.SelectedIndex = -1
68 txtStudentName.Text = ""
69 txtSection.Text = ""
70 txtAdmissionNo.Text = ""
71 txtClass.Text = ""
72 txtSchoolName.Text = ""
73 cmbLocationName.SelectedIndex = -1
74 btnSave.Enabled = True
75 btnUpdate.Enabled = False
76 btnDelete.Enabled = False
77 txtAdmissionNo.Focus()
78 auto()
79 End Sub
80 Private Sub btnNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNew.Click
81 Reset()
82 End Sub
83
84 Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
85 If Len(Trim(txtAdmissionNo.Text)) = 0 Then
86 MessageBox.Show("Please retrieve admission no.", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
87 txtAdmissionNo.Focus()
88 Exit Sub
89 End If
90 If Len(Trim(cmbBusNo.Text)) = 0 Then
91 MessageBox.Show("Please select bus no.", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
92 cmbBusNo.Focus()
93 Exit Sub
94 End If
95 If Len(Trim(cmbLocationName.Text)) = 0 Then
96 MessageBox.Show("Please select Location name", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
97 cmbLocationName.Focus()
98 Exit Sub
99 End If
100 If Len(Trim(cmbStatus.Text)) = 0 Then
101 MessageBox.Show("Please select status", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
102 cmbStatus.Focus()
103 Exit Sub
104 End If
105 Try
106 con = New SqlConnection(cs)
107 con.Open()
108 Dim ct As String = "select AdmissionNo from BusCardHolder_Student where AdmissionNo=@d1"
109 cmd = New SqlCommand(ct)
110 cmd.Connection = con
111 cmd.Parameters.AddWithValue("@d1", txtAdmissionNo.Text)
112 rdr = cmd.ExecuteReader()
113 If rdr.Read Then
114 MessageBox.Show("Record already exists", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
115 Reset()
116 If Not rdr Is Nothing Then
117 rdr.Close()
118 End If
119 Exit Sub
120 End If
121 con = New SqlConnection(cs)
122 con.Open()
123 Dim cb As String = "insert into BusCardHolder_Student(BCH_ID,AdmissionNo, Location, JoiningDate, Status,BusNo) VALUES (" & txtID.Text & ",@d1,@d2,@d3,@d4,@d5)"
124 cmd = New SqlCommand(cb)
125 cmd.Connection = con
126 cmd.Parameters.AddWithValue("@d1", txtAdmissionNo.Text)
127 cmd.Parameters.AddWithValue("@d2", cmbLocationName.Text)
128 cmd.Parameters.AddWithValue("@d3", CDate(dtpJoiningDate.Text))
129 cmd.Parameters.AddWithValue("@d4", cmbStatus.Text)
130 cmd.Parameters.AddWithValue("@d5", cmbBusNo.Text)
131 cmd.ExecuteNonQuery()
132 LogFunc(lblUser.Text, "added new bus holder '" & txtStudentName.Text & "' having admission no. '" & txtAdmissionNo.Text & "'")
133 MessageBox.Show("Successfully saved", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
134 btnSave.Enabled = False
135 con.Close()
136 Catch ex As Exception
137 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
138 End Try
139 End Sub
140
141 Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
142 If Len(Trim(txtAdmissionNo.Text)) = 0 Then
143 MessageBox.Show("Please retrieve admission no.", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
144 txtAdmissionNo.Focus()
145 Exit Sub
146 End If
147 If Len(Trim(cmbBusNo.Text)) = 0 Then
148 MessageBox.Show("Please select bus no.", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
149 cmbBusNo.Focus()
150 Exit Sub
151 End If
152 If Len(Trim(cmbLocationName.Text)) = 0 Then
153 MessageBox.Show("Please select Location name", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
154 cmbLocationName.Focus()
155 Exit Sub
156 End If
157 If Len(Trim(cmbStatus.Text)) = 0 Then
158 MessageBox.Show("Please select status", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
159 cmbStatus.Focus()
160 Exit Sub
161 End If
162 Try
163 con = New SqlConnection(cs)
164 con.Open()
165 Dim cb As String = "Update BusCardHolder_Student set AdmissionNo=@d1, Location=@d2, JoiningDate=@d3, Status=@d4,BusNo=@d5 where BCH_ID=" & txtID.Text & ""
166 cmd = New SqlCommand(cb)
167 cmd.Connection = con
168 cmd.Parameters.AddWithValue("@d1", txtAdmissionNo.Text)
169 cmd.Parameters.AddWithValue("@d2", cmbLocationName.Text)
170 cmd.Parameters.AddWithValue("@d3", CDate(dtpJoiningDate.Text))
171 cmd.Parameters.AddWithValue("@d4", cmbStatus.Text)
172 cmd.Parameters.AddWithValue("@d5", cmbBusNo.Text)
173 cmd.ExecuteNonQuery()
174 LogFunc(lblUser.Text, "updated the record of bus holder '" & txtStudentName.Text & "' having admission no. '" & txtAdmissionNo.Text & "'")
175 MessageBox.Show("Successfully updated", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
176 btnUpdate.Enabled = False
177 con.Close()
178 Catch ex As Exception
179 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
180 End Try
181 End Sub
182
183 Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
184 Try
185 If MessageBox.Show("Do you really want to delete the record?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) = Windows.Forms.DialogResult.Yes Then
186 delete_records()
187 End If
188 Catch ex As Exception
189 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
190 End Try
191 End Sub
192 Private Sub delete_records()
193 Try
194 Dim RowsAffected As Integer = 0
195 con = New SqlConnection(cs)
196 con.Open()
197 Dim cl As String = "select BusHolderID from BusCardHolder_Student,BusFeePayment_Student where BusCardHolder_Student.BCH_ID=BusFeePayment_Student.BusHolderID and BusHolderID=@d1"
198 cmd = New SqlCommand(cl)
199 cmd.Connection = con
200 cmd.Parameters.AddWithValue("@d1", txtID.Text)
201 rdr = cmd.ExecuteReader()
202 If rdr.Read Then
203 MessageBox.Show("Unable to delete..Already in use in Bus Fee Payment [Student]", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
204 If Not rdr Is Nothing Then
205 rdr.Close()
206 End If
207 Exit Sub
208 End If
209 con = New SqlConnection(cs)
210 con.Open()
211 Dim cq As String = "delete from BusCardHolder_Student where BCH_ID= " & txtID.Text & ""
212 cmd = New SqlCommand(cq)
213 cmd.Connection = con
214 RowsAffected = cmd.ExecuteNonQuery()
215 If RowsAffected > 0 Then
216 LogFunc(lblUser.Text, "deleted the bus holder '" & txtStudentName.Text & "' having admission no. '" & txtAdmissionNo.Text & "'")
217 MessageBox.Show("Successfully deleted", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
218 Reset()
219 Else
220 MessageBox.Show("No record found", "Sorry", MessageBoxButtons.OK, MessageBoxIcon.Information)
221 Reset()
222 If con.State = ConnectionState.Open Then
223
224 con.Close()
225 End If
226
227 con.Close()
228 End If
229 Catch ex As Exception
230 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
231 End Try
232 End Sub
233
234 Private Sub frmBusCardHolder_Student_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
235 fillLocationName()
236 fillBusNo()
237 End Sub
238
239 Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
240 frmStudentRecord.Reset()
241 frmStudentRecord.lblSet.Text = "Bus Holder Entry"
242 frmStudentRecord.ShowDialog()
243 End Sub
244
245 Private Sub btnGetData_Click(sender As System.Object, e As System.EventArgs) Handles btnGetData.Click
246 frmBusCardHolder_StudentRecord.Reset()
247 frmBusCardHolder_StudentRecord.lblSet.Text = "Bus Holder Entry"
248 frmBusCardHolder_StudentRecord.ShowDialog()
249 End Sub
250 End Class
